package database.table;

import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import javax.sql.rowset.serial.SerialBlob;
import javax.swing.table.AbstractTableModel;

import database.Database;

public class DBTableData extends AbstractTableModel {
	private final Database 		db;
	private String 				name;
	private int 				keyColumn;
	private ArrayList<String>	colName;
	private ArrayList<Class<?>>	colType;
	private ArrayList<Boolean>	colShow;
	private ArrayList<ArrayList<Object>>	data;
	
	public DBTableData(String name, Database db) throws SQLException {
		ResultSet	result;
		Statement	st;
		String 		field;
		Class<?>	type;
		
		int		ncol;
		boolean	first;
		
		System.out.println("Creating table model '" + name + "'");
		
		// Setup initial fields
		this.db = db;
		this.name = name;
		
		// Execute DESCRIBE query to obtain column data
		st = db.con.createStatement();
		result = st.executeQuery("DESCRIBE " + name);
		
		// Setup table header
		colName = new ArrayList<String>();
		colType = new ArrayList<Class<?>>();
		colShow = new ArrayList<Boolean>();
		keyColumn = -1;
		
		while (result.next()) {
			field = result.getString("field");
			type = Database.getTypeNameType(result.getString("type"));
			
			colName.add(field);
			colType.add(type);
			colShow.add(type != SerialBlob.class);
			
			if (result.getString("key").equalsIgnoreCase("pri")) {
				keyColumn = colName.size() - 1;
			}
		}
		
		ncol = colName.size();

		// Obtain table data
		String query = "SELECT ";
		first = true;
		for (int c = 0; c < ncol; c++) {
			if (colShow.get(c)) {
				query += (first) ? (colName.get(c)) : (", " + colName.get(c));
				first = false;
			}
		}
		query += " FROM " + name;// + " LIMIT 20";

		ResultSet rs = st.executeQuery(query);
		
		// Store table data
		data = new ArrayList<ArrayList<Object>>();
		ArrayList<Object> row;
		while (rs.next()) {
			row = new ArrayList<Object>(ncol);
			for (int c = 0; c < ncol; c++) {
				if (colShow.get(c)) {
					row.add(rs.getObject(colName.get(c)));
				}
				else
					row.add(null);
			}
			data.add(row);
		}
	}
	
	public int addColumn( String name, Class<?> type ) {
		if (colName.contains(name))
			return -1;
		
		colName.add(name);
		colType.add(type);
		colShow.add(true);
		for (int i = 0; i < getRowCount(); i++)
			data.get(i).add(null);
		
		fireTableStructureChanged();
		return colName.size()-1;
	}
	
	@Override
	public Class<?> getColumnClass(int col) {
		return colType.get(col);
	}

	@Override
	public int getColumnCount() {
		return colName.size();
	}
	
	public int getColumnIndex( String name ) {
		return colName.indexOf(name);
	}

	@Override
	public String getColumnName(int col) {
		if (col >= 0 && col < colName.size())
			return colName.get(col);
		
		return null;
	}

	@Override
	public int getRowCount() {
		return data.size();
	}

	public String getName() {
		return name;
	}
	
	@Override
	public Object getValueAt(int row, int col) {
		if (colShow.get(col))
			return data.get(row).get(col);
		
		return "...";
	}
	
	public Object getValueAt( int row, String colName ) {
		int col = getColumnIndex(colName);
		
		if (col == -1)
			return null;
		
		return getValueAt(row, col);
	}
	
	public Object getValueAtId( Object id, String colName ) {
		if (colType.get(keyColumn).isInstance(id)) {
			for (int row = 0; row < getRowCount(); row++) {
				if (data.get(row).get(keyColumn).equals(id))
					return getValueAt(row, colName);
			}
		}
		
		return null;
	}
	
	public InputStream getBlobStreamAt( int row, String col ) {
		Statement st;
		ResultSet result;
		try {
			st = db.con.createStatement();
			result = st.executeQuery("SELECT " + col + " FROM " + name + " WHERE id = " + getValueAt(row, 0));
			
			if (result.next())
				return result.getBinaryStream(col);

			return null;
		}
		catch (SQLException e) {
			System.out.println("ERROR: could not get blob stream");
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public String getStringAt( int row, String colName ) {
		int col = getColumnIndex(colName);
		
		if (colType.get(col) == String.class)
			return (String) getValueAt(row, col);
		
		return null;
	}
	
	public int getIntegerAt( int row, String colName ) {
		int col = getColumnIndex(colName);
		
		if (colType.get(col) == Integer.class)
			return (Integer) getValueAt(row, col);
		
		return 0;
	}

	@Override
	public boolean isCellEditable(int row, int col) {
		return colShow.get(col);
	}
	
	@Override
	public void setValueAt(Object x, int row, int col) {
		data.get(row).set(col, x);
	}

	public void sort(int col) {
		DBTableComparator comparator = new DBTableComparator(col);
		Collections.sort(data, comparator);
	}
	
	public void commit( String col ) {
		int vcol = getColumnIndex(col);
		
		if (vcol == -1) {
			System.out.println("ERROR: column " + colName + " does not exist");
			return;
		}
		
		Statement st = db.createStatement();
		String cmd;
		
		for (int row = 0; row < getRowCount(); row++) {
			try {
				cmd = "UPDATE " + name + " SET " + col + " = '" + getValueAt(row, vcol) +
						"' WHERE id = " + getValueAt(row, 0);
				
				st.executeUpdate(cmd);
				System.out.println(cmd);
			} catch (Exception e) {
				System.out.println("ERROR: error on update");
				System.out.println(e.getMessage());
			}
		}
	}

	private class DBTableComparator implements Comparator<ArrayList<Object>> {
		int col;
		
		public DBTableComparator(int col) {
			this.col = col;
		}
		
		@Override
		public int compare(ArrayList<Object> o1, ArrayList<Object> o2) {
			Object val1 = o1.get(col);
			Object val2 = o2.get(col);
			
			if (val1 instanceof Comparable)
				return ((Comparable) (val1)).compareTo(val2);
			
			return 0;
		}
		
	}
}
